﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.IO;
using System.Xml;
using System.Data.SqlClient;


namespace Server
{
    /// <summary>
    /// Summary description for Service2
    /// </summary>
    [WebService(Namespace = "http://localhost:6442/se137/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    [System.Web.Script.Services.ScriptService]
    public class Service2 : System.Web.Services.WebService
    {

        private double originalLat;
        private double originalLong;
        private double maxDistance;

        private double DegreeToRadian(double val)
        {
            return Math.PI * val / 180.0;
        }


        public double getDistance(double dLat, double dLong)
        {
            double earthRad = 6371;
            double milesConversionFactor = 0.621371192;

            //return ((Math.PI / 180) * degrees);
            double lat = (dLat - originalLat);
            lat = (Math.PI / 180) * lat;

            double longi = (dLong - originalLong);
            longi = (Math.PI / 180) * longi;

            double a = Math.Sin(lat / 2) * Math.Sin(lat / 2)
                + Math.Cos(DegreeToRadian(originalLat)) * Math.Cos(DegreeToRadian(dLat)) *
                Math.Sin(longi / 2) * Math.Sin(longi / 2);

            double c = 2 * Math.Atan2(Math.Sqrt(a), Math.Sqrt(1 - a));
            double d = earthRad * c;
            return d * milesConversionFactor;
        }

        private void setOriginalLatLong(string zipcode)
        {
            // Open DB Connection
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();


            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT latitude, longitude from ZIP where zip=" + zipcode;

            SqlDataReader rdr = cmd.ExecuteReader();
            if (rdr.HasRows)
            {
                rdr.Read();
                originalLat = (double)rdr.GetDecimal(rdr.GetOrdinal("Latitude"));
                originalLong = (double)rdr.GetDecimal(rdr.GetOrdinal("longitude"));

            }

            rdr.Close();
            conn.Close();

        }

        [WebMethod]
        public string HelloWorld()
        {
            return "Hello World";
        }

        /*
         * SELECT B.BID as 'BusinessId',
            B.name as 'BusinessName',
            (B.address1 + B.address2 + B.address3 + ' ' + B.city + ', CA') as 'Address', 
            CB.CATID as 'CatId',
            C.name AS 'Specialty',
            B.phone as 'Phone',
            T.AVERAGE_REVIEW as 'Rating'
            FROM BUSINESSES B, CATEGORIZEDBUS CB, CATEGORIES C,
            (SELECT BID, AVG(rating) AS 'AVERAGE_REVIEW' from REVIEWS GROUP BY BID) T
            WHERE B.BID = CB.BID AND
            C.CATID = CB.CATID
            AND
            T.BID = B.BID
            AND
            B.city = 'Saratoga'
            AND
            T.AVERAGE_REVIEW > 2
            AND
            C.name IN ('Family Practice', 'Medical Spas', 'Hospitals', 'Doctors')
         */
        private static string[] searchResults = {
                                "BusinessId",
                                "BusinessName",
                                "Address",
                                "CatId",
                                "Specialty",
                                "Phone",
                                "Rating"};

        private static string connectionString = Conn.connectionString;
        [WebMethod] //city, speciality and average rating
        public string SearchResults(string zipcode, string specialty, string rating, string distance) 
        {

            //set the origin point if one is provided
            if ((zipcode != null) && (zipcode.Length > 0))
            {
                setOriginalLatLong(zipcode);
            }

            if ((distance != null) && (distance.Length > 0))
            {
                maxDistance = Double.Parse(distance);
                Console.WriteLine("max distance = " + maxDistance);
            }

            // Open DB Connection
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();

            string paramlist = "";

            //specialties
            if ((specialty != null) && (specialty.Length > 0))
            {
                paramlist += " AND C.name = '" + specialty + "' ";
            }

            //add rating
            //syntax and rating > 
            if ((rating != null) && (rating.Length > 0)) {
                paramlist += " AND T.AVERAGE_REVIEW >= " + rating;
            }

            string previous = "";

            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT B.BID as 'BusinessId',  " + 
                "B.name as 'BusinessName',   " + 
                "(B.address1 + B.address2 + B.address3 + ' ' + B.city + ', CA') as 'Address',    " + 
                "CB.CATID as 'CatId',   " + 
                "C.name AS 'Specialty',   " + 
                "B.phone as 'Phone',   " + 
                "T.AVERAGE_REVIEW as 'Rating',   " + 
                "D.latitude as 'Latitude',   " + 
                "D.longitude as 'Longitude' " + 
                "FROM BUSINESSES B, CATEGORIZEDBUS CB, CATEGORIES C, zip D , " + 
                "(SELECT BID, AVG(rating) AS 'AVERAGE_REVIEW' from REVIEWS GROUP BY BID) T   " + 
                "WHERE B.BID = CB.BID AND   " + 
                "C.CATID = CB.CATID   " + 
                "AND D.ZIP = B.ZIP AND " +
                "T.BID = B.BID " + 
                paramlist + 
                " ORDER BY B.BID";
            
            SqlDataReader rdr = cmd.ExecuteReader();

            // Start the XML
            StringWriter stringWriter = new StringWriter();
            XmlTextWriter xmltextWriter = new XmlTextWriter(stringWriter);

            xmltextWriter.Formatting = Formatting.Indented;
            // Start document
            xmltextWriter.WriteStartDocument();
            xmltextWriter.WriteStartElement("root");

            int count =0;

            while (rdr.Read())
            {

                //if the value in the business does not match the previous value
                if (!(rdr.GetValue(rdr.GetOrdinal("BusinessId")).ToString().Equals(previous)))
                {
                    //first business skip the end tag
                    if (count > 0)
                    {
                        xmltextWriter.WriteEndElement();  //end business
                    }
                    else
                    {
                        count++;
                    }

                    xmltextWriter.WriteStartElement("business");  //start business

                    //add all the other information
                    foreach (string field in searchResults)
                    {
                        try
                        {
                            xmltextWriter.WriteElementString(field, rdr.GetValue(rdr.GetOrdinal(field)).ToString());
                        }
                        catch (Exception ex)
                        {
                            System.Console.Out.WriteLine(field);
                        }
                    }

                    if ((distance != null) && (distance.Length > 0))
                    {
                        try
                        {
                            //get the distance between the origin and the desired zip code
                            double lat = Convert.ToDouble(rdr.GetValue(rdr.GetOrdinal("Latitude")).ToString());
                            double lon = Convert.ToDouble(rdr.GetValue(rdr.GetOrdinal("Longitude")).ToString());
                            double distanceFrom = getDistance(lat, lon);

                            //add the distance from tag only iff the distance between is below that required by user
                            if (distanceFrom <= maxDistance)
                            {
                                xmltextWriter.WriteElementString("Distance", Convert.ToString(distanceFrom));
                            }
                            else 
                            {
                                xmltextWriter.WriteElementString("Distance", "-1");
                            }
                        }
                        catch (Exception e)
                        {
                            Console.WriteLine(e.StackTrace);
                        }

                    }

                    //assign the previous as the current business id
                    previous = rdr.GetValue(rdr.GetOrdinal("BusinessId")).ToString();
                    
                }
                else
                {
                    //otherwise add the category if the business id is the same
                    xmltextWriter.WriteElementString("CatId", rdr.GetValue(rdr.GetOrdinal("CatId")).ToString());
                }
            }

            if (count > 0) xmltextWriter.WriteEndElement();  //end last business
            xmltextWriter.WriteEndElement();  //end Root
            // Close Connection

            rdr.Close();
            conn.Close();

            // End Document
            xmltextWriter.WriteEndDocument();

            xmltextWriter.Flush();
            xmltextWriter.Close();
            stringWriter.Flush();

            return stringWriter.ToString();
        }
    }
}
